﻿#Region "Namespaces"
Imports System
Imports System.Threading
Imports System.Collections.Generic
Imports System.Runtime.InteropServices
Imports System.Text
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Win32
Imports System.Xml
Imports System.IO
Imports System.Drawing.Graphics
Imports System.ComponentModel
#End Region

'Test save

Module GlobalVar
    'Global Declaration of Variables to be used
    '--------------------------SQL Database Connection Variables-----------------------------
    'Public SQLQuery_Text As String
    Public SQL_Server_Name As String 'For example - Data Source=SAM-HPLAPTOP\SQLEXPRESS
    Public SQL_DB_Name As String 'For Example - Database=SylvaniaDB
    Public SQL_DB_Username As String
    Public SQL_DB_Password As String
    Public SQL_DB_WinAuth As Boolean
    Public Bulk_UpLoads_Folder As String
    Public csb As New SqlConnectionStringBuilder()
    Public cmd As New SqlCommand
    Public dr As SqlDataReader
    Public SQLQuery As String = ""
    Public SQLCount As String = ""
    Public SQL_Rows_Retrieved As Integer

    'Declare vars for Search Text boxes
    Public date_start_text As String = ""
    Public date_end_text As String = ""
    Public serial_start_text As String = ""
    Public serial_end_text As String = ""
    Public operator_start_text As String = ""
    Public operator_end_text As String = ""
    Public programming_field_text As String = ""
    Public sw_rev_text As String = ""
    Public limit_rev_text As String = ""
    Public test_mode_text As String = ""
    Public limit_temp_text As String = ""
    Public pass_fail_text As String = ""
    Public comments_field_text As String = ""
    Public TSSN_Start_text As String = ""
    Public TSSN_End_text As String = ""
    Public Test_Station_SN_text As String = ""

    'Declare vars to hold sql command text
    Public date_start_sql As String = ""
    Public date_end_sql As String = ""
    Public serial_start_sql As String = ""
    Public serial_end_sql As String = ""
    Public operator_start_sql As String = ""
    Public operator_end_sql As String = ""
    Public programming_field_sql As String = ""
    Public sw_rev_sql As String = ""
    Public limit_rev_sql As String = ""
    Public test_mode_sql As String = ""
    Public limit_temp_sql As String = ""
    Public pass_fail_sql As String = ""
    Public comments_field_sql As String = ""
    Public TSSN_Start_SQL As String = ""
    Public TSSN_End_SQL As String = ""
    Public Create_Table_SQL As String = ""
    Public Update_Table_SQL As String = ""
    Public Bulk_Insert_ID As Integer = 0
    Public Return_MAX_Limits_ID As String = ""
    Public Limits_ID_1 As String = "0"
    Public Limits_ID_2 As String = "0"
    Public Limits_ID_3 As String = "0"
    Public Limits_ID_4 As String = "0"
    Public Limits_ID_5 As String = "0"
    Public Limits_ID_6 As String = "0"
    Public Limits_ID_7 As String = "0"
    Public Limits_ID_8 As String = "0"
    Public Limits_ID_9 As String = "0"
    Public Limits_ID_10 As String = "0"
    Public Limits_ID_11 As String = "0"
    Public Limits_ID_12 As String = "0"

    'Assign a variable to hold the number of columns in the table
    Public Number_of_Columns As Integer = 0

    'Assign Variable to hold Minitab Path
    Public Minitab_Path As String

    'Assign Variable to hold Splitter Position
    Public Splitter_Position As Integer = 438

    'Variable to let ConnectionString Form know if it is being called from the Search Results form
    Public CSF_Called_From_SearchResults As Integer = 0
    '---------------------------------------------------------------------------------------
    'Security Variables
    'Following Vars tell secure functions whether the password has already been entered or not
    Public Admin_PW_Entered As String = "N"
    Public EditRec_PW_Entered As String = "N"
    Public DelRec_PW_Entered As String = "N"
    'Public Import_PW_Entered As String = "N"
    'Following vars hold the current passwords from the DB
    Public SecretWord_PW As String = ""
    Public Admin_PW As String = ""
    Public EditRec_PW As String = ""
    Public DelRec_PW As String = ""
    'Public Import_PW As String = ""
    '----------------------------------------------------------------------------------------
    'Variables to Exit loading records Loop in Search btnclick
    Public Loading_Records_Cancel As String = "N"
    Public SR As New Search_Results
    Public Loading_Records_Dialog

    'Progress Bar Vars
    Public DGV_Total_Rows As Integer = 0
    Public DGV_CurrentRow As Integer = 0
    Public Percent As Integer = 0
    '-----------------------------------------------------------------------------------------
    'Variables to determine if Previous button was clicked
    Public ReqDate_Prev As String = "N"
    Public CompDate_Prev As String = "N"
    Public ShipDate_Prev As String = "N"
    Public BuildQty_Prev As String = "N"
    Public BuildType_Prev As String = "N"
    Public BOMRev_Prev As String = "N"
    Public PCBRev_Prev As String = "N"
    Public PCBZnn_Prev As String = "N"
    Public PCBAssm_Prev As String = "N"
    Public PCBManu_Prev As String = "N"
    Public LEDSpec_Prev As String = "N"
    Public BuildIns_Prev As String = "N"
    Public Customer_Prev As String = "N"
    Public ShippingRecord_Prev As String = "N"
    Public Status_Prev As String = "N"
    Public Comments_Prev As String = "N"
    '----------------------------------------END----------------------------------------------
End Module

Public Class ESBD_Form

#Region "Declare Variables"
    'Declare Vars For Main Form
    Dim LabviewFile As String
    Dim LabviewFileTrimmed As String
    Dim LimitFile As String
    'Dim Update_LimitFile As String
    Dim Update_Limit_FileName As String
    Dim Limit_FileName As String
    Dim Data_Filename As String
    Dim Table_Name As String = ""
    Dim Create_Table_Name As String = ""
    Dim Update_Limits_Table_Name As String = ""
    Dim Revision_Date As String = ""
    Dim Update_Revision_Date As String = ""
    Dim Converted_Time As String = ""
#End Region

#Region "Form Load/Close Routines"

    Private Sub ESBD_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Set Function_PW_Entered = N
        Admin_PW_Entered = "N"
        EditRec_PW_Entered = "N"
        DelRec_PW_Entered = "N"
        'Import_PW_Entered = "N"

        'Show version number in window title if the application has been installed/deployed
        If (System.Deployment.Application.ApplicationDeployment.IsNetworkDeployed) Then
            Dim Current_Version As System.Version = My.Application.Deployment.CurrentVersion
            Me.Text = "Engineering Sample Build Database - v." + Current_Version.ToString
        End If

        'On initial load of main form, if any connection string settings are null, then open Connection Strings form 
        My.Settings.Reload()
        SQL_DB_Name = My.Settings.Setting_SQL_DB_Name
        SQL_DB_Password = My.Settings.Setting_SQL_DB_Password
        SQL_Server_Name = My.Settings.Setting_SQL_DB_Server
        SQL_DB_Username = My.Settings.Setting_SQL_DB_Username
        SQL_DB_WinAuth = My.Settings.Setting_SQL_DB_WinAuth
        

        'Check if DB Connection, Security and ESBD_Main tables have been setup
        Check_Settings()

        'Delete Temp folders
        Try
            Dim Delete_FilenamePath As String = "C:\ESBD_Temp\"
            If (System.IO.Directory.Exists(Delete_FilenamePath)) Then
                System.IO.Directory.Delete(Delete_FilenamePath, True)
            End If
        Catch ex As Exception
            Console.WriteLine("Files are still open")
        End Try

Finish:
        Close_Splash()
        Me.Show()
        Me.Cursor = Cursors.Default
    End Sub
    Private Sub ESBD_Form_close(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.FormClosed
        'My.Settings.Setting_SplitterPosition = SplitContainer1.SplitterDistance
        My.Settings.Save()
        Admin_PW = ""
        EditRec_PW = ""
        DelRec_PW = ""
        'Import_PW = ""
        SecretWord_PW = ""

        'Delete Temp folder
        Try
            Dim Delete_FilenamePath As String = "C:\ESBD_Temp\"
            If (System.IO.Directory.Exists(Delete_FilenamePath)) Then
                System.IO.Directory.Delete(Delete_FilenamePath, True)
            End If
        Catch ex As Exception
            Console.WriteLine("Files are still open")
        End Try

    End Sub
    Private Sub Check_Settings()
        'If ((SQL_DB_Name = "") Or (SQL_DB_Username = "") Or (SQL_DB_Password = "") Or (SQL_Server_Name = "")) Then
        If ((SQL_DB_Name = "") Or (SQL_Server_Name = "")) Then
            ConnectionStringForm.ShowDialog()

            If ((SQL_DB_Name = "") Or (SQL_Server_Name = "")) Then
                MsgBox("Cannot connect to server. Please check connection settings.", MsgBoxStyle.OkOnly)
            Else
                'Query Database and display records to comboboxes
            End If
            
        Else
            ConnectionStringForm.cmbServers.Text = SQL_Server_Name
            ConnectionStringForm.cmbDatabases.Text = SQL_DB_Name
            ConnectionStringForm.TB_SQL_Username_Text.Text = SQL_DB_Username
            ConnectionStringForm.TB_SQL_Password_Text.Text = SQL_DB_Password

            If My.Settings.Setting_SQL_DB_WinAuth = True Then
                ConnectionStringForm.ChkWinAuth.Checked = True
            End If

            If My.Settings.Setting_SQL_DB_WinAuth = False Then
                ConnectionStringForm.ChkWinAuth.Checked = False
            End If

            'Create security and esbd_main tables
            Create_Tables()

        End If
    End Sub

    Public Sub Create_Tables()
        'Check if Security table exists. If not, create the security table and then check to see if ESBD_Main table exists. If not call Create_ESBD_Main_Table routine.
        'If security table exists, check if ESBD_Main table exists, if not call Create_ESBD_Main_Table routine.
        Try
            Dim SQLResult As Object
            Dim Records_Retrieved As String = 0

            OpenSQLConnection()
            Dim connection As New SqlConnection(csb.ToString())
            cmd.Connection = connection
            cmd.CommandText = "Select count(*) from sys.tables where Name='Security'"

            connection.Open()
            SQLResult = cmd.ExecuteScalar
            Records_Retrieved = SQLResult
            If Records_Retrieved = 0 Then
                Create_Security_Table()
                connection.Close()
                Application.Restart()

                'Check if ESBD_Main table exists
                Try
                    OpenSQLConnection()
                    cmd.Connection = connection
                    cmd.CommandText = "Select count(*) from sys.tables where Name='ESBD_Main'"

                    connection.Open()
                    SQLResult = cmd.ExecuteScalar
                    Records_Retrieved = SQLResult
                    If Records_Retrieved = 0 Then
                        connection.Close()
                        Create_ESBD_Main_Table()
                    Else
                        connection.Close()
                        'Application.Restart()
                    End If
                    connection.Close()
                    'Application.Restart()
                Catch ex As Exception
                    connection.Close()
                    MsgBox(ex.message)
                End Try
            Else
                'Retrieve Passwords and assign them to the global security variables
                Assign_Passwords()
                connection.Close()

                'Check if ESBD_Main table exists
                Create_ESBD_Main_Table()
            End If
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.message)
        End Try
    End Sub

    Private Sub Create_ESBD_Main_Table()
        Dim SQLResult As Object
        Dim Records_Retrieved As String = 0

        OpenSQLConnection()
        Dim connection As New SqlConnection(csb.ToString())
        cmd.Connection = connection
        cmd.CommandText = "Select count(*) from sys.tables where Name='ESBD_Main'"

        connection.Open()
        SQLResult = cmd.ExecuteScalar
        Records_Retrieved = SQLResult
        If Records_Retrieved = 0 Then
            'Create ESBD_Main, Project_TB, and CUST_TB tables
            Try
                cmd.CommandText = "CREATE TABLE ESBD_Main ( [Job_ID] [int] NOT NULL, [Project_Name] [varchar](100) NOT NULL, [Requested_Date] [datetime] NOT NULL, [Completion_Date] [datetime] NOT NULL, [Ship_Date] [datetime] NULL, [Build_Quantity] [int] NOT NULL, [Build_Type] [varchar](50) NOT NULL, [BOM_Rev] [varchar](50) NULL, [PCB_Rev] [varchar](50) NULL, [PCB_ZNN] [int] NULL, [PCB_Manufacturer] [varchar](50) NOT NULL, [PCB_Assembler] [varchar](50) NOT NULL, [LED_Specification] [varchar](max) NULL, [Special_Build_Instruction] [varchar](max) NULL, [Customer] [varchar](max) NULL, [Shipping_Record] [nvarchar](100) NULL, [Status] [varchar](50) NOT NULL, [Comments] [varchar](max) NULL );"
                cmd.ExecuteScalar()

                Dim Primary_Key_SQL = "ALTER TABLE ""ESBD_Main"" ADD PRIMARY KEY(Project_Name, Build_Quantity, Build_Type);"
                cmd.CommandText = Primary_Key_SQL
                cmd.ExecuteScalar()

                connection.Close()
            Catch ex As Exception
                connection.Close()
                MsgBox(ex.message)
            End Try

            'Try
            '    OpenSQLConnection()
            '    Dim conn As New SqlConnection(csb.ToString())
            '    cmd.Connection = conn
            '    conn.Open()

            '    cmd.CommandText = Create_Table_SQL
            '    cmd.ExecuteScalar()
            '    '-----------------------------------------------------------
            '    'Add Alter Tables statements to set Date, Time, and Serial_Number to not nullable
            '    'Dim Alter_Column_Date = "ALTER TABLE """ + Create_Table_Name + """" + vbCrLf + "Alter COLUMN Date Datetime NOT NULL"
            '    'Dim Alter_Column_Time = "AlTER TABLE """ + Create_Table_Name + """" + vbCrLf + "ALTER COLUMN TIME char(50) NOT NULL"
            '    'Dim Alter_Column_Serial_Number = "AlTER TABLE """ + Create_Table_Name + """" + vbCrLf + "ALTER COLUMN Serial_Number char(50) NOT NULL"

            '    '------------------------------------------------------------
            '    'Create Primary keys to prevent duplicate values in Date, Time, and Serial_Number
            '    'Dim Primary_Key_SQL = "ALTER TABLE ""ESBD_Main"" ADD PRIMARY KEY(Project_Name, Build_Quantity, Build_Type);"

            '    'Create Unique Index so that if a duplicate value is encountered in the Date, Time, or Serial_Number column, then warning messages are not shown so that data insertion can complete without errors.
            '    'Dim Create_Unique_Index_SQL = "CREATE UNIQUE INDEX uq_idx_date_time_serial_number" + vbCrLf + "ON """ + Create_Table_Name + """(Date, Time, Serial_Number)" + vbCrLf + "WITH ignore_dup_key"

            '    '-------------------------------------------------------------
            '    'Execute Alter statements
            '    'cmd.CommandText = Alter_Column_Date
            '    'cmd.ExecuteScalar()

            '    'cmd.CommandText = Alter_Column_Time
            '    'cmd.ExecuteScalar()

            '    'cmd.CommandText = Alter_Column_Serial_Number
            '    'cmd.ExecuteScalar()

            '    'cmd.CommandText = Primary_Key_SQL
            '    'cmd.ExecuteScalar()

            '    'cmd.CommandText = Create_Unique_Index_SQL
            '    'cmd.ExecuteScalar()

            '    conn.Close()
            'Catch ex As Exception
            '    Dim conn As New SqlConnection(csb.ToString())
            '    conn.Close()
            '    MsgBox(ex.message)
            'End Try

        Else
            'Do Nothing
            connection.Close()
        End If

        'Check if Project_TB table exists
        cmd.CommandText = "Select count(*) from sys.tables where Name='Project_TB'"
        connection.Open()
        SQLResult = cmd.ExecuteScalar
        Records_Retrieved = SQLResult
        If Records_Retrieved = 0 Then
            'Create Project_TB table
            Try
                cmd.CommandText = "CREATE TABLE Project_TB ( [PJ_Name] [varchar](max) NOT NULL );"
                cmd.ExecuteScalar()
                connection.Close()
            Catch ex As Exception
                connection.Close()
                MsgBox(ex.message)
            End Try
        Else
            'Do Nothing
            connection.Close()
        End If

        'Check if Customer table exists
        cmd.CommandText = "Select count(*) from sys.tables where Name='CUST_TB'"
        connection.Open()
        SQLResult = cmd.ExecuteScalar
        Records_Retrieved = SQLResult
        If Records_Retrieved = 0 Then
            'Create Project_TB table
            Try
                cmd.CommandText = "CREATE TABLE CUST_TB ( [Name] [varchar](50) NOT NULL, [Street1] [varchar](50) NULL, [Street2] [varchar](50) NULL, [City] [varchar](50) NULL, [State] [varchar](50) NULL, [Country] [varchar](50) NULL, [ZipCode] [varchar](10) NULL, [Notes] [varchar](max) NULL );"
                cmd.ExecuteScalar()
                connection.Close()
            Catch ex As Exception
                connection.Close()
                MsgBox(ex.message)
            End Try
        Else
            'Do Nothing
            connection.Close()
        End If

        'Check if Build Type Table Exists
        cmd.CommandText = "Select count(*) from sys.tables where Name='BuildType_TB'"
        connection.Open()
        SQLResult = cmd.ExecuteScalar
        Records_Retrieved = SQLResult
        If Records_Retrieved = 0 Then
            'Create BuildType_TB table
            Try
                cmd.CommandText = "CREATE TABLE BuildType_TB ( [Type] [varchar](10) NOT NULL );"
                cmd.ExecuteScalar()

                'Add default Entries
                cmd.CommandText = "INSERT INTO BuildType_TB ( [Type] ) VALUES('A');"
                cmd.ExecuteScalar()
                cmd.CommandText = "INSERT INTO BuildType_TB ( [Type] ) VALUES('B');"
                cmd.ExecuteScalar()
                cmd.CommandText = "INSERT INTO BuildType_TB ( [Type] ) VALUES('C');"
                cmd.ExecuteScalar()
                cmd.CommandText = "INSERT INTO BuildType_TB ( [Type] ) VALUES('Proto');"
                cmd.ExecuteScalar()

                connection.Close()
            Catch ex As Exception
                connection.Close()
                MsgBox(ex.message)
            End Try
        Else
            'Do Nothing
            connection.Close()
        End If

        'Check if PCB Manufacturer Table Exists
        cmd.CommandText = "Select count(*) from sys.tables where Name='PCBManu_TB'"
        connection.Open()
        SQLResult = cmd.ExecuteScalar
        Records_Retrieved = SQLResult
        If Records_Retrieved = 0 Then
            'Create PCBManu_TB table
            Try
                cmd.CommandText = "CREATE TABLE PCBManu_TB ( [Name] [varchar](50) NOT NULL );"
                cmd.ExecuteScalar()
                connection.Close()
            Catch ex As Exception
                connection.Close()
                MsgBox(ex.message)
            End Try
        Else
            'Do Nothing
            connection.Close()
        End If

        'Check if PCB Assembler Table Exists
        cmd.CommandText = "Select count(*) from sys.tables where Name='PCBAssm_TB'"
        connection.Open()
        SQLResult = cmd.ExecuteScalar
        Records_Retrieved = SQLResult
        If Records_Retrieved = 0 Then
            'Create PCBAssm_TB table
            Try
                cmd.CommandText = "CREATE TABLE PCBAssm_TB ( [Name] [varchar](50) NOT NULL );"
                cmd.ExecuteScalar()
                connection.Close()
            Catch ex As Exception
                connection.Close()
                MsgBox(ex.message)
            End Try
        Else
            'Do Nothing
            connection.Close()
        End If

        'Check if Status_TB Table Exists
        cmd.CommandText = "Select count(*) from sys.tables where Name='Status_TB'"
        connection.Open()
        SQLResult = cmd.ExecuteScalar
        Records_Retrieved = SQLResult
        If Records_Retrieved = 0 Then
            'Create PCBManu_TB table
            Try
                cmd.CommandText = "CREATE TABLE Status_TB ( [Name] [varchar](50) NOT NULL );"
                cmd.ExecuteScalar()
                connection.Close()
            Catch ex As Exception
                connection.Close()
                MsgBox(ex.message)
            End Try
        Else
            'Do Nothing
            connection.Close()
        End If

        'Check if Attachments_TB Table Exists
        cmd.CommandText = "Select count(*) from sys.tables where Name='Attachments_TB'"
        connection.Open()
        SQLResult = cmd.ExecuteScalar
        Records_Retrieved = SQLResult
        If Records_Retrieved = 0 Then
            'Create Attachments_TB table
            Try
                cmd.CommandText = "CREATE TABLE Attachments_TB ( [ID] [int] IDENTITY(1,1) NOT NULL, [Filename] [nvarchar] (100) NOT NULL, [Extension] [nvarchar] (5) NOT NULL, [Content] [varbinary] (max) NULL );"
                cmd.ExecuteScalar()
                connection.Close()
            Catch ex As Exception
                connection.Close()
                MsgBox(ex.message)
            End Try
        Else
            'Do Nothing
            connection.Close()
        End If

    End Sub

    Public Sub Create_Security_Table()
        'Create Security Table
        cmd.CommandText = "CREATE TABLE Security ( Username char(50), Password VARCHAR(50) );"
        cmd.ExecuteScalar()

        'Make Password Column Not Null
        cmd.CommandText = "ALTER TABLE ""Security"" Alter COLUMN Password VARCHAR(50) NOT NULL"
        cmd.ExecuteScalar()

        'Insert Rows
        cmd.CommandText = "INSERT INTO Security (Username, Password) VALUES('Admin','admin');"
        cmd.ExecuteScalar()
        cmd.CommandText = "INSERT INTO Security (Username, Password) VALUES('EditRec','');"
        cmd.ExecuteScalar()
        cmd.CommandText = "INSERT INTO Security (Username, Password) VALUES('DelRec','');"
        cmd.ExecuteScalar()
        cmd.CommandText = "INSERT INTO Security (Username, Password) VALUES('SecretWord','admin');"
        cmd.ExecuteScalar()

        'Assign Security Variables the passwords
        SecretWord_PW = "admin"
        Admin_PW = "admin"
        EditRec_PW = ""
        DelRec_PW = ""
        'Import_PW = ""
    End Sub

    Private Sub Assign_Passwords()
        Dim SQLPassword As Object
        Dim Result As String = ""

        Try
            cmd.CommandText = "SELECT Password from Security where Username = 'Admin'"
            SQLPassword = cmd.ExecuteScalar
            If Convert.IsDBNull(SQLPassword) Then
                SQLPassword = ""
            End If
            Result = SQLPassword
            Admin_PW = Result
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.message)
        End Try

        Try
            cmd.CommandText = "SELECT Password from Security where Username = 'EditRec'"
            SQLPassword = cmd.ExecuteScalar
            If Convert.IsDBNull(SQLPassword) Then
                SQLPassword = ""
            End If
            Result = SQLPassword
            EditRec_PW = Result
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.message)
        End Try

        Try
            cmd.CommandText = "SELECT Password from Security where Username = 'DelRec'"
            SQLPassword = cmd.ExecuteScalar
            If Convert.IsDBNull(SQLPassword) Then
                SQLPassword = ""
            End If
            Result = SQLPassword
            DelRec_PW = Result
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.message)
        End Try

        'Try
        '    cmd.CommandText = "SELECT Password from Security where Username = 'Import'"
        '    SQLPassword = cmd.ExecuteScalar
        '    If Convert.IsDBNull(SQLPassword) Then
        '        SQLPassword = ""
        '    End If
        '    Result = SQLPassword
        '    Import_PW = Result
        'Catch ex As Exception
        '    Dim connection As New SqlConnection(csb.ToString())
        '    connection.Close()
        '    MsgBox(ex.message)
        'End Try

        Try
            cmd.CommandText = "SELECT Password from Security where Username = 'SecretWord'"
            SQLPassword = cmd.ExecuteScalar
            If Convert.IsDBNull(SQLPassword) Then
                SQLPassword = ""
            End If
            Result = SQLPassword
            SecretWord_PW = Result
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.message)
        End Try
    End Sub
#End Region

#Region "Button-Click Routines"
    Private Sub BTN_NewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_NewRecord.Click

        'Determine if Previous button was clicked
        ReqDate_Prev = "N"
        CompDate_Prev = "N"
        ShipDate_Prev = "N"
        BuildQty_Prev = "N"
        BuildType_Prev = "N"
        BOMRev_Prev = "N"
        PCBRev_Prev = "N"
        PCBZnn_Prev = "N"
        PCBAssm_Prev = "N"
        PCBManu_Prev = "N"
        LEDSpec_Prev = "N"
        BuildIns_Prev = "N"
        Customer_Prev = "N"
        ShippingRecord_Prev = "N"
        Status_Prev = "N"
        Comments_Prev = "N"

        'Refresh ComboBoxes
        Refresh_ComboBoxes()

        'Clear(TextBoxes)
        Completion_Date.TB_CompDate.Text = ""
        Ship_Date.TB_ShipDate.Text = ""
        Build_Qty.TB_BuildQty.Text = ""
        Build_Type.TB_BuildTypeNumber.Text = ""
        Build_Type.CB_Proto.Checked = False
        BOM_Rev.TB_BOMRev.Text = ""
        PCB_Rev.TB_PCBRev.Text = ""
        PCB_ZNN.TB_PCBZNN.Text = ""
        LED_Spec.TB_LEDSpec.Text = ""
        Build_Ins.TB_BuildIns.Text = ""
        Shipping_Record.TB_FilePath.Text = ""
        Comments.TB_Comments.Text = ""
        Project_Name.cmb_ProjName.Text = ""
        Build_Type.cmb_BuildType.Text = ""
        PCB_Manu.cmb_PCBManu.Text = ""
        PCB_Assembler.cmb_PCBAssm.Text = ""
        Customer.cmb_Customer.Text = ""
        Status.cmb_Status.Text = ""

        'Display dialog forms
Previous1:
        Project_Name.ShowDialog()
        If Project_Name.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            GoTo Finish
        End If

Previous2:
        Req_Date.ShowDialog()
        If Req_Date.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If ReqDate_Prev = "Y" Then
                ReqDate_Prev = "N"
                GoTo Previous1
            Else
                GoTo Finish
            End If
        End If

Previous3:
        Completion_Date.ShowDialog()
        If Completion_Date.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If CompDate_Prev = "Y" Then
                CompDate_Prev = "N"
                GoTo Previous2
            Else
                GoTo Finish
            End If
        End If

Previous4:
        Ship_Date.ShowDialog()
        If Ship_Date.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If ShipDate_Prev = "Y" Then
                ShipDate_Prev = "N"
                GoTo Previous3
            Else
                GoTo Finish
            End If
        End If

Previous5:
        Build_Qty.ShowDialog()
        If Build_Qty.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If BuildQty_Prev = "Y" Then
                BuildQty_Prev = "N"
                GoTo Previous4
            Else
                GoTo Finish
            End If
        End If

Previous6:
        Build_Type.ShowDialog()
        If Build_Type.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If BuildType_Prev = "Y" Then
                BuildType_Prev = "N"
                GoTo Previous5
            Else
                GoTo Finish
            End If
        End If

Previous7:
        BOM_Rev.ShowDialog()
        If BOM_Rev.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If BOMRev_Prev = "Y" Then
                BOMRev_Prev = "N"
                GoTo Previous6
            Else
                GoTo Finish
            End If
        End If

Previous8:
        PCB_Rev.ShowDialog()
        If PCB_Rev.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If PCBRev_Prev = "Y" Then
                PCBRev_Prev = "N"
                GoTo Previous7
            Else
                GoTo Finish
            End If
        End If

Previous9:
        PCB_ZNN.ShowDialog()
        If PCB_ZNN.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If PCBZnn_Prev = "Y" Then
                PCBZnn_Prev = "N"
                GoTo Previous8
            Else
                GoTo Finish
            End If
        End If

Previous10:
        PCB_Manu.ShowDialog()
        If PCB_Manu.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If PCBManu_Prev = "Y" Then
                PCBManu_Prev = "N"
                GoTo Previous9
            Else
                GoTo Finish
            End If
        End If

Previous11:
        PCB_Assembler.ShowDialog()
        If PCB_Assembler.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If PCBAssm_Prev = "Y" Then
                PCBAssm_Prev = "N"
                GoTo Previous10
            Else
                GoTo Finish
            End If
        End If

Previous12:
        LED_Spec.ShowDialog()
        If LED_Spec.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If LEDSpec_Prev = "Y" Then
                LEDSpec_Prev = "N"
                GoTo Previous11
            Else
                GoTo Finish
            End If
        End If

Previous13:
        Build_Ins.ShowDialog()
        If Build_Ins.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If BuildIns_Prev = "Y" Then
                BuildIns_Prev = "N"
                GoTo Previous12
            Else
                GoTo Finish
            End If
        End If

Previous14:
        Customer.ShowDialog()
        If Customer.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If Customer_Prev = "Y" Then
                Customer_Prev = "N"
                GoTo Previous13
            Else
                GoTo Finish
            End If
        End If

Previous15:
        Shipping_Record.ShowDialog()
        If Shipping_Record.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If ShippingRecord_Prev = "Y" Then
                ShippingRecord_Prev = "N"
                GoTo Previous14
            Else
                GoTo Finish
            End If
        End If

Previous16:
        Status.ShowDialog()
        If Status.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If Status_Prev = "Y" Then
                Status_Prev = "N"
                GoTo Previous15
            Else
                GoTo Finish
            End If
        End If

Previous17:
        Comments.ShowDialog()
        If Comments.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            If Comments_Prev = "Y" Then
                Comments_Prev = "N"
                GoTo Previous16
            Else
                GoTo Finish
            End If
        End If

        NewRecord_Form.ShowDialog()
        If NewRecord_Form.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            GoTo Finish
        End If

Finish:

    End Sub
    Private Sub Btn_InsertValues_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_InsertValues.Click
        'Ask for password
        If Admin_PW_Entered = "Y" Then
            GoTo PW_Accepted
        Else
            Dim Passwd As String
            Passwd = InputBox("Please enter Administrator password.", "Add Items Password")
            If Passwd = Admin_PW Then
                Admin_PW_Entered = "Y"
                EditRec_PW_Entered = "Y"
                DelRec_PW_Entered = "Y"
                GoTo PW_Accepted
            ElseIf Passwd = "" Then
                GoTo Finish
            Else
                MsgBox("Sorry the password you entered did not match the password on file." + vbCrLf + "Please check spelling and capitalization and try again.", MsgBoxStyle.Information)
                GoTo Finish
            End If
        End If
        
PW_Accepted:
        AddItems.ShowDialog()
Finish:
    End Sub
    Private Sub BTN_SearchRecords_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_SearchRecords.Click
        Search_Form.ShowDialog()
    End Sub
    Private Sub BTN_EditCust_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_EditCust.Click
        Customer_Details.BTN_Save.Visible = True
        Customer_Details.BTN_Cancel2.Visible = True
        Customer_Details.LBL_Name.Text = "Select"
        Customer_Details.cmb_CustName.Visible = True
        Customer_Details.GroupBox2.Text = "Select Customer to Edit"
        Customer_Details.TLP_OkCancel.Visible = False
        Customer_Details.TB_Name.Visible = False

        'Clear Text Fields
        Customer_Details.TB_Add1.Text = ""
        Customer_Details.TB_Add2.Text = ""
        Customer_Details.TB_City.Text = ""
        Customer_Details.TB_Country.Text = ""
        Customer_Details.TB_Notes.Text = ""
        Customer_Details.TB_State.Text = ""
        Customer_Details.TB_Zip.Text = ""
        Customer_Details.cmb_CustName.Select()

        'Fill customer name combobox
        Try
            'Clear ComboBox
            Customer_Details.cmb_CustName.Items.Clear()

            'Refresh ComboBox
            OpenSQLConnection()
            Dim connection As New SqlConnection(csb.ToString())
            cmd.Connection = connection
            connection.Open()

            'Repopulate CUST_TB
            cmd.CommandText = "Select * from ""CUST_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                Customer_Details.cmb_CustName.Items.Add(dr.GetString(0))
            Loop
            dr.Close()
            connection.Close()

            'Sort the Combobox
            Customer_Details.cmb_CustName.Sorted = True
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.Message)
        End Try

        'Open Customer Details Form
        Customer_Details.ShowDialog()
    End Sub
#End Region

#Region "Public Functions"
    <DllImport("mpr.dll")> Private Shared Function WNetGetConnection(ByVal drive As String, ByVal buf As StringBuilder, ByRef buflen As Integer) As Integer
        'To find UNC path
        'Imports System.Runtime.InteropServices Required
    End Function

    Public Sub Close_Splash()
        If My.Application.SplashScreen IsNot Nothing Then

            Dim splashScreenDispose As New My.MyApplication.DisposeDelegate(AddressOf My.Application.SplashScreen.Dispose)

            My.Application.SplashScreen.Invoke(splashScreenDispose)

            My.Application.SplashScreen = Nothing

            'Me.TopMost = True

            Me.Activate()

        End If
    End Sub

    Public Sub Pause(ByVal dblSecs As Double)
        'Allow Pause in code. Pause 1 = 1 seconds, Pause 2 = 2 seconds, etc...
        Const OneSec As Double = 1.0# / (1440.0# * 60.0#)
        Dim dblWaitTil As Date
        Now.AddSeconds(OneSec)
        dblWaitTil = Now.AddSeconds(OneSec).AddSeconds(dblSecs)
        Do Until Now > dblWaitTil
            Application.DoEvents() ' Allow windows messages to be processed
        Loop
    End Sub

    Public Sub OpenSQLConnection()
        'Open SQL Connection

        'If Checkbox Windows Authentication Checked, then
        If SQL_DB_WinAuth = True Then
            csb.DataSource = SQL_Server_Name 'SQL_DB_Server
            csb.IntegratedSecurity = "True"
            csb.InitialCatalog = SQL_DB_Name
            'They want to go with Windows Authentication instead. With Windows Authentication, there is no need for a username and password field - (csb.UserID = SQL_DB_Username csb.Password = SQL_DB_Password)
        Else
            If SQL_DB_WinAuth = False Then
                csb.DataSource = SQL_Server_Name 'SQL_DB_Server
                csb.IntegratedSecurity = "False"
                csb.InitialCatalog = SQL_DB_Name
                csb.UserID = SQL_DB_Username
                csb.Password = SQL_DB_Password
            End If
        End If
    End Sub

    Public Function Convert_Time(ByVal readTime As String) As String

        Try
            Dim Original_Time As String = readTime
            Dim int As Integer = 0
            Dim Modified_Original_Time As String = ""
            Dim Hour As String()
            Dim Position1 As Integer
            Dim Search_String1 As String = "AM"

            'Determine if string contains AM or PM
            Position1 = InStr(Original_Time, Search_String1)

            If Position1 <> 0 Then
                'Time is AM. Get hour from Original_Time
                'Remove Last 3 chars from Original_Time
                int = ((Original_Time.Length) - 3)

                For i = 0 To int
                    Modified_Original_Time = Modified_Original_Time + Original_Time.Chars(i)
                Next
                Modified_Original_Time = Trim(Modified_Original_Time)

                'Split Modified_Original_Time by the character ":" and store it into an array
                Hour = Modified_Original_Time.Split(":")

                'Since it is AM, we want to convert 12 to 00 if the first element in the array is 12
                If Hour(0) = 12 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "00"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 1 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "01"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 2 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "02"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 3 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "03"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 4 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "04"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 5 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "05"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 6 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "06"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 7 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "07"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 8 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "08"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 9 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "09"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If
                Converted_Time = Modified_Original_Time

            Else
                'Time is PM
                'Remove Last 3 chars from Original_Time
                int = ((Original_Time.Length) - 3)

                For i = 0 To int
                    Modified_Original_Time = Modified_Original_Time + Original_Time.Chars(i)
                Next
                Modified_Original_Time = Trim(Modified_Original_Time)

                'Split Modified_Original_Time by the character ":" and store it into an array
                Hour = Modified_Original_Time.Split(":")

                'Since it is PM, we want to convert 1-11 to 13-23 if the first element in the array is 1-11
                If Hour(0) = 1 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "13"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 2 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "14"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 3 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "15"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 4 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "16"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 5 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "17"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 6 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "18"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 7 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "19"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 8 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "20"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 9 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "21"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 10 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "22"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If

                If Hour(0) = 11 Then
                    'Clear Modified_Original_Time
                    Modified_Original_Time = ""
                    Hour(0) = "23"
                    For i = 0 To 2
                        If i = 2 Then
                            Modified_Original_Time = Modified_Original_Time + Hour(i)
                        Else
                            Modified_Original_Time = Modified_Original_Time + Hour(i) + ":"
                        End If
                    Next
                End If
                Converted_Time = Modified_Original_Time
            End If
        Catch ex As Exception
            MsgBox(ex.message)
            Return vbNullString
        End Try
        Return vbNullString
    End Function

    Public Sub Refresh_ComboBoxes()
        'Query Database and display records to comboboxes
        Try
            'Clear ComboBoxes
            Project_Name.cmb_ProjName.Items.Clear()
            Build_Type.cmb_BuildType.Items.Clear()
            PCB_Manu.cmb_PCBManu.Items.Clear()
            PCB_Assembler.cmb_PCBAssm.Items.Clear()
            Status.cmb_Status.Items.Clear()
            Customer.cmb_Customer.Items.Clear()

            'Refresh ComboBoxes
            OpenSQLConnection()
            Dim connection As New SqlConnection(csb.ToString())
            cmd.Connection = connection
            connection.Open()

            'Repopulate Project_TB
            cmd.CommandText = "Select * from ""Project_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                Project_Name.cmb_ProjName.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            'Repopulate Status_TB
            cmd.CommandText = "Select * from ""Status_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                Status.cmb_Status.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            'Repopulate BuildType_TB
            cmd.CommandText = "Select * from ""BuildType_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                Build_Type.cmb_BuildType.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            'Repopulate PCBManu_TB
            cmd.CommandText = "Select * from ""PCBManu_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                PCB_Manu.cmb_PCBManu.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            'Repopulate PCBAssm_TB
            cmd.CommandText = "Select * from ""PCBAssm_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                PCB_Assembler.cmb_PCBAssm.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            'Repopulate CUST_TB
            cmd.CommandText = "Select * from ""CUST_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                Customer.cmb_Customer.Items.Add(dr.GetString(0))
            Loop
            dr.Close()
            connection.Close()

            'Sort the Comboboxes
            Project_Name.cmb_ProjName.Sorted = True
            Build_Type.cmb_BuildType.Sorted = True
            PCB_Manu.cmb_PCBManu.Sorted = True
            PCB_Assembler.cmb_PCBAssm.Sorted = True
            Status.cmb_Status.Sorted = True
            Customer.cmb_Customer.Sorted = True
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.message)
        End Try
    End Sub

    Public Function ListItem_Exists(ByVal TBL_Name As String, ByVal Column As String, ByVal New_Entry As String) As Boolean

        Dim SQLResult As Object
        Dim Summary As Integer
        Dim conn As New SqlConnection(csb.ToString())
        Try
            OpenSQLConnection()
            cmd.Connection = conn
            cmd.CommandText = "Select Count(*) from """ + TBL_Name + """ where """ + Column + """ = '" + New_Entry + "'"

            conn.Open()
            SQLResult = cmd.ExecuteScalar()
            Summary = SQLResult
            conn.Close()

            If Summary = 0 Then
                Return False
            Else
                Return True
            End If
        Catch ex As Exception
            MsgBox(ex.message)
            Return vbNullString
        End Try
    End Function

#End Region

#Region "File Menu Items"

    Private Sub MI_SetNew_PW_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MI_SetNew_PW.Click
        Dim AdminPasswd As String
        AdminPasswd = InputBox("Please enter Administrator Password to proceed", "Administrator Password")
        If AdminPasswd = Admin_PW Then
            Admin_PW_Entered = "Y"
            EditRec_PW_Entered = "Y"
            DelRec_PW_Entered = "Y"
            Security_Setup.ShowDialog()
        ElseIf AdminPasswd = "" Then
            GoTo End_Sub
        Else
            MsgBox("Sorry the password you entered did not match the password on file." + vbCrLf + "Please check spelling and capitalization and try again.", MsgBoxStyle.Information)
            GoTo End_Sub
        End If
End_Sub:
    End Sub
    Private Sub MI_Restore_Passwords_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MI_Restore_Passwords.Click
        'Ask for Admin password or the secret word
        Dim Passwd As String
        Passwd = InputBox("Please enter Administrator Password or the Secret Word to reset passwords to original settings.", "Reset Passwords")
        If Passwd = Admin_PW Or Passwd = SecretWord_PW Then
            Admin_PW_Entered = "Y"
            EditRec_PW_Entered = "Y"
            DelRec_PW_Entered = "Y"
            'Import_PW_Entered = "Y"

            'Set original Passwords
            Admin_PW = "admin"
            EditRec_PW = ""
            DelRec_PW = ""
            SecretWord_PW = "admin"

            'Write new passwords to DB
            Try
                OpenSQLConnection()
                Dim connection As New SqlConnection(csb.ToString())
                cmd.Connection = connection
                connection.Open()

                cmd.CommandText = "UPDATE ""Security"" set Password = 'admin' where Username = 'Admin'"
                cmd.ExecuteScalar()

                cmd.CommandText = "UPDATE ""Security"" set Password = '' where Username = 'EditRec'"
                cmd.ExecuteScalar()

                cmd.CommandText = "UPDATE ""Security"" set Password = '' where Username = 'DelRec'"
                cmd.ExecuteScalar()

                cmd.CommandText = "UPDATE ""Security"" set Password = 'admin' where Username = 'SecretWord'"
                cmd.ExecuteScalar()
                connection.Close()
            Catch ex As Exception
                Dim connection As New SqlConnection(csb.ToString())
                connection.Close()
                MsgBox(ex.message)
            End Try

            MsgBox("Passwords have been restored to their original values", MsgBoxStyle.Information)
            Security_Setup.ShowDialog()
        ElseIf Passwd = "" Then
            GoTo Close
        Else
            MsgBox("Sorry the password you entered did not match the password on file." + vbCrLf + "Please check spelling and capitalization and try again.", MsgBoxStyle.Information)
            GoTo Close
        End If
Close:
    End Sub
    Private Sub SetupToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SetupToolStripMenuItem.Click
        Me.Cursor = Cursors.AppStarting
        ConnectionStringForm.ShowDialog()
        Me.Cursor = Cursors.Default
    End Sub
    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        Me.Close()
    End Sub
    Private Sub ClearSettingsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClearSettingsToolStripMenuItem.Click
        'Clear out connection string saved values

        'MsgBox(My.Settings.Setting_SQL_DB_Name + ", " + My.Settings.Setting_SQL_DB_Password + ", " + My.Settings.Setting_SQL_DB_Server + ", " + My.Settings.Setting_SQL_DB_Username + ", " + My.Settings.Setting_SQL_DB_WinAuth.ToString())
        SQL_DB_Name = ""
        SQL_DB_Password = ""
        SQL_Server_Name = ""
        SQL_DB_Username = ""
        'SQL_DB_WinAuth = ""
        My.Settings.Setting_SQL_DB_Name = ""
        My.Settings.Setting_SQL_DB_Password = ""
        My.Settings.Setting_SQL_DB_Server = ""
        My.Settings.Setting_SQL_DB_Username = ""
        'My.Settings.Setting_Minitab_Path = ""
        My.Settings.Save()
        MsgBox("Connection settings have been deleted!", MsgBoxStyle.OkOnly)
    End Sub
    Private Sub AddChangeImageToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddChangeImageToolStripMenuItem.Click
        Dim filepath As String
        'Display file open dialog to let user choose limit file
        OpenFD.FileName = ""
        OpenFD.Filter = "Supported File Types|*.jpeg; *.gif; *.jpg; *.bmp; *.png; *.tif;"
        Dim FD_Result As Integer = OpenFD.ShowDialog()

        If FD_Result = DialogResult.Cancel Then
            GoTo End_Sub
        End If

        'Assigns Full path and name of file to the variable AttachFile
        filepath = OpenFD.FileName

        'Show Picture
        Me.PictureBox1.Image = New System.Drawing.Bitmap(filepath)

End_Sub:
    End Sub
#End Region

End Class
